package com.hongdee.crm.contact.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.contact.dao.ITCrmCarContactDao;
import com.hongdee.crm.contact.entity.TCrmCarContact;
import com.hongdee.crm.contact.entity.TCrmCarContactExtend;
import com.hongdee.crm.contact.entity.TCrmCarContactResult;
import com.hongdee.crm.contact.entity.TCrmCarTaskHelp;
import com.hongdee.crm.contact.mapper.IConcatMapper;
import com.hongdee.crm.contact.pojo.TCrmConcatVo;
import com.hongdee.crm.contact.service.ITCrmCarContactService;
import com.hongdee.crm.maintain.entity.TCrmCarMaintain;
import com.hongdee.crm.task.dao.ITCrmTaskDao;
import com.hongdee.crm.task.entity.TCrmTaskReal;
import com.hongdee.crm.task.service.ITCrmCarMaintainMileTaskService;

import jdk.nashorn.internal.parser.Token;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by My on 2017/2/28.
 */
@Service
public class TCrmCarContactServiceImpl implements ITCrmCarContactService {
    private String tabName = "T_CRM_CAR_CONTACT";
    private String tableName = "T_CRM_TASK_REAL";
    private String port = ":9001/";
    @Autowired
    private ITCrmCarContactDao itCrmCarContactDao;
    @Autowired
    private IConcatMapper concatMapper;

    @Autowired
    private ITCrmTaskDao crmTaskDao;

    @Autowired
    private ITCrmCarMaintainMileTaskService itCrmCarMaintainMileTaskService;

    /**
     * @param start 当前页面
     * @param limit 页面大小
     * @param data  筛选集合
     * @return Map
     * @throws Exception
     * @desc 维系记录，分页查询
     * @author 徐钦勇
     */
    @Override
    public PageInfo page(int page, int limit, Map data, String uid) throws Exception {
        PageHelper.startPage(page, limit);
        List ls = concatMapper.query(data, uid);
        PageInfo<TCrmConcatVo> pageInfo = new PageInfo<>(ls);
        return pageInfo;
    }

    /**
     * 维系记录查询sql
     *
     * @param sqlBuf
     * @param params
     * @param data
     * @param uid
     * @throws Exception
     */
    private void concatPageToSql(StringBuffer sqlBuf, List params, Map data, String uid) throws Exception {
        sqlBuf.append("SELECT                                    ");
        sqlBuf.append("cc.manitain_sender,     /*送修人*/        ");
        sqlBuf.append("cc.sender_tel,        /*送修人电话*/    ");
        sqlBuf.append("cc.model model,         /*车型*/          ");
        sqlBuf.append("ct.vin,                 /*vin*/           ");
        sqlBuf.append("ct.next_date,        /*持续跟进时间*/           ");
        sqlBuf.append("ct.next_content,        /*持续跟进内容*/           ");
        sqlBuf.append("cc.owner_name,          /*车主姓名*/      ");
        sqlBuf.append("tu.u_name,              /*客户经理*/      ");
        sqlBuf.append("ct.plate_num,           /*车牌号*/        ");
        sqlBuf.append("ct.record_path,           /*录音地址*/");
        sqlBuf.append(" tg.company_ip_address,case when length(tg.company_ip_address)>0 and length(ct.record_path)>8  then concat('http://',tg.company_ip_address,'/',SUBSTR(ct.record_path,8)) else ct.record_path end  as record_patha , ");
        sqlBuf.append("ct.title,               /*维系类别*/      ");
        sqlBuf.append("ct.content,			   /*维系内容*/      ");
        sqlBuf.append("ct.record_length,       /*通话时长*/      ");
        sqlBuf.append("ct.create_time,         /*时间*/          ");
        sqlBuf.append("ct.new_remind,          /*预约*/          ");
        sqlBuf.append("ct.sa_id,               /*saID*/          ");
        sqlBuf.append("ct.new_remind,          /*预约类型*/      ");
        sqlBuf.append("ct.parent_id,            /*维系大类*/      ");
        sqlBuf.append("cc.brand,               /*品牌*/          ");
        sqlBuf.append("cc.series,              /*车系*/          ");
        sqlBuf.append("cc.configure,           /*车型*/          ");
        sqlBuf.append("cc.last_maintain_date, /*进厂时间*/");
        sqlBuf.append("cc.maintain_start_mile,  /*维修起始里程*/");
        sqlBuf.append(" CASE " +
                "WHEN exd.visit_result ='success' THEN '成功' " +
                "WHEN exd.visit_result ='fail' THEN '失败' end as visit_result, ");
        sqlBuf.append(" CASE " +
                "WHEN exd.task_pcode = 'zh' THEN " +
                " '综合' " +
                "WHEN exd.task_pcode = 'by' THEN " +
                " '保养' " +
                "WHEN exd.task_pcode = 'bx' THEN " +
                " '保险' " +
                "WHEN exd.task_pcode = 'ls' THEN " +
                " '流失' " +
                "WHEN exd.task_pcode = 'ga' THEN " +
                " '关爱' " +
                "END AS task_pcode, " +
                " CASE " +
                "WHEN exd.task_code = 'sc' THEN " +
                " '手册' " +
                "WHEN exd.task_code = 'xbx' THEN " +
                " '新保续' " +
                "WHEN exd.task_code = 'xubx' THEN " +
                " '续保续' " +
                "WHEN exd.task_code = 'hy' THEN " +
                " '活跃' " +
                "WHEN exd.task_code = 'fhy' THEN " +
                " '非活跃' " +
                "WHEN exd.task_code = 'sr' THEN " +
                " '生日' " +
                "WHEN exd.task_code = 'ns' THEN " +
                " '年审' " +
                "WHEN exd.task_code = 'rc' THEN " +
                " '日常' " +
                "WHEN exd.task_code = 'yb' THEN " +
                " '延保' " +
                "WHEN exd.task_code = 'hd' THEN " +
                " '活动' " +
                "WHEN exd.task_code = 'srhf' THEN " +
                " '三日回访' " +
                "WHEN exd.task_code = 'zls' THEN " +
                " '准流失' " +
                "WHEN exd.task_code = 'yls' THEN " +
                " '流失' " +
                "WHEN exd.task_code = 'sb' THEN " +
                " '首保' " +
                "WHEN exd.task_code = 'sbm' THEN " +
                " '目标月首保' " +
                "WHEN exd.task_code = 'eb' THEN " +
                " '二保' " +
                "WHEN exd.task_code = 'ebm' THEN " +
                " '目标月二保' " +
                "WHEN exd.task_code = 'db' THEN " +
                " '定保' " +
                "END AS task_code, " +
                " exd.maintain_adress, " +
                " CASE " +
                "WHEN exd.lost_reason = 'clzr' THEN " +
                " '车辆转让或报废' " +
                "WHEN exd.lost_reason = 'rzwd' THEN " +
                " '人在外地没时间保养' " +
                "WHEN exd.lost_reason = 'glc' THEN " +
                " '过路车' " +
                "WHEN exd.lost_reason = 'chsy' THEN " +
                " '车很少用' " +
                "WHEN exd.lost_reason = 'fbr' THEN " +
                " '非车主本人接听电话' " +
                "WHEN exd.lost_reason = 'qdc' THEN " +
                " '外地车' " +
                "WHEN exd.lost_reason = 'wxzl' THEN " +
                " '维修质量' " +
                "WHEN exd.lost_reason = 'wxjg' THEN " +
                " '维修价格' " +
                "WHEN exd.lost_reason = 'wxxl' THEN " +
                " '维修效率' " +
                "WHEN exd.lost_reason = 'fwtd' THEN " +
                " '服务态度' " +
                "WHEN exd.lost_reason = 'fwhj' THEN " +
                " '服务环境' " +
                "WHEN exd.lost_reason = 'lcy' THEN " +
                " '路程远' " +
                "WHEN exd.lost_reason = 'qt' THEN " +
                " '其他' " +
                "END AS lost_reason, " +
                " case WHEN exd.intention_level ='lcwd'  THEN '里程未到'            " +
                "WHEN exd.intention_level ='jqdd'  THEN '近期到店'            " +
                "WHEN exd.intention_level ='zjtm'  THEN '最近太忙没时间进店'          " +
                "WHEN exd.intention_level ='yyyh'  THEN '已预约好近期到店' end as intention_Level,  " +
                "case WHEN exd.intention_enter='yes' then '是'  " +
                "  WHEN exd.intention_enter='no' then '否' end as intention_enter  " +
                ",case WHEN exd.ismaintained='yes' then '是'  " +
                "  WHEN exd.ismaintained='no' then '否' end as ismaintained," +
                " CASE " +
                "WHEN exd.fail_reason = 'dhtj' THEN " +
                " '电话错误停机或空号' " +
                "WHEN exd.fail_reason = 'rybf' THEN " +
                " '人员不符' " +
                "WHEN exd.fail_reason = 'wjt' THEN " +
                " '未接通' " +
                "WHEN exd.fail_reason = 'khjf' THEN " +
                " '客户拒访' " +
                "END AS fail_reason ");
        sqlBuf.append("from T_CRM_CAR_CONTACT ct                 ");
        sqlBuf.append("LEFT JOIN t_crm_car cc on ct.vin=cc.vin ");
        sqlBuf.append("LEFT JOIN t_bc_user tu on ct.sa_id=tu.id  ");
        sqlBuf.append("LEFT JOIN t_crm_car_contact_extend exd ON ct.id=exd.c_id ");
        sqlBuf.append("LEFT JOIN t_bc_organ tg ON ct.company_id=tg.id ");

        sqlBuf.append("where  ct.company_id = ? ");

        //传入的公司
        String reqCompanyId = data.get("companyId") == null ? "" : data.get("companyId").toString();
        if (reqCompanyId.length() == 0) {
            params.add(TokenUtil.getCompanyId(uid));
        } else {
            params.add(reqCompanyId);
        }

        //起始时间
        Map maptime = (HashMap) data.get("date");
        if (maptime.get("startTime") != "") {
            String startTime = String.valueOf(maptime.get("startTime"));
            startTime = startTime.substring(0, 10);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Long startTimeLong = sdf.parse(startTime).getTime();
            sqlBuf.append(" and ct.create_time >= ?  ");
            params.add(startTimeLong);
        }
        if (maptime.get("stopTime") != "") {
            String stopTime = String.valueOf(maptime.get("stopTime"));
            //Long startTimeLong = DateUtils.parseTimeZoneStr(stopTime);
            stopTime = stopTime.substring(0, 10) + " 23:59:59";
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Long stopTimeLong = sdf.parse(stopTime).getTime();
            sqlBuf.append(" and ct.create_time <= ?  ");
            params.add(stopTimeLong);
        }
//             if(maptime.get("startTime")==""&&maptime.get("stopTime")==""){
//                 SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");
//                 String nowDate = sdf.format(new Date());
//                 sqlBuf.append(" and FROM_UNIXTIME(ct.create_time/1000,'yyyy-MM') = ? ");
//                 params.add(nowDate);
//            }
        //车主姓名
        String name = String.valueOf(data.get("name"));
        if (!name.isEmpty()) {
            sqlBuf.append(" and cc.owner_name like ? ");
            params.add("%" + name + "%");
        }
        //
        String vin = String.valueOf(data.get("vin"));
        if (!vin.isEmpty()) {
            sqlBuf.append(" and ct.vin like ? ");
            params.add("%" + vin + "%");
        }
        //车牌号
        String pn = String.valueOf(data.get("plateNum"));
        if (!pn.isEmpty()) {
            sqlBuf.append(" and ct.plate_num like ? ");
            params.add("%" + pn + "%");
        }
        //选择人员
        //if(TokenUtil.isCompanyAdmin(uid)&&data.get("saId")!=null)
        String sa = String.valueOf(data.get("saId"));
        //是否是管理员
//        String resRoleSQL = "select distinct r.id,r.is_manger from t_bc_role r ,t_bc_role_user ru where r.id = ru.r_id and ru.u_id=?";
//        List<Map<String, Object>> rseList = this.itCrmCarContactDao.list(resRoleSQL, uid);
//        boolean isManger = false;
//        for (Map<String, Object> temp : rseList) {
//            if ("1".equals(String.valueOf(temp.get("isManger")))) {
//                isManger = true;
//            }
//        }
        boolean isManger = TokenUtil.isCompanyAdmin(uid);
        //选择SA并且是管理员
        if (!sa.isEmpty() && isManger) {
            sqlBuf.append(" and ct.sa_id = ? ");
            params.add(sa);
        }
        //选择SA并且不是管理员&&

        if (!sa.isEmpty() && !isManger) {
            if (sa.equals(uid)) {
                sqlBuf.append(" and ct.sa_id = ? ");
                params.add(uid);
            } else if (!reqCompanyId.equals(TokenUtil.getCompanyId(uid))) {
                sqlBuf.append(" and ct.sa_id = ? ");
                params.add(sa);
            } else {
                throw new Exception("该用户不是管理员");
            }

        }
        //不选SA并且不是管理员
        if (sa.isEmpty() && !isManger && reqCompanyId.equals(TokenUtil.getCompanyId(uid))) {
            sqlBuf.append(" and ct.sa_id = ? ");
            params.add(uid);
        }
        //选择时长 0:0-30s,1:30-60s,2:1-2min,3:2-3min,4:3-5min,5:>5min
        String recordLength = String.valueOf(data.get("recordLength"));
        if (!recordLength.isEmpty()) {
            switch (recordLength) {
                case "0": {
                    sqlBuf.append(" and (ct.record_length between '0' and '30') ");
                }
                break;
                case "1": {
                    sqlBuf.append(" and (ct.record_length between '30' and '60') ");
                }
                break;
                case "2": {
                    sqlBuf.append(" and (ct.record_length between '60' and '120') ");
                }
                break;
                case "3": {
                    sqlBuf.append(" and (ct.record_length between '120' and '180') ");
                }
                break;
                case "4": {
                    sqlBuf.append(" and (ct.record_length between '180' and '300') ");
                }
                break;
                case "5": {
                    sqlBuf.append(" and ct.record_length > '300'");
                }
                break;
            }
        }
        //预约类型 0未预约，1已预约
        String yy = String.valueOf(data.get("newRemind"));
        if (!yy.isEmpty()) {
            sqlBuf.append(" and ct.new_remind = ? ");
            params.add(yy);
        }
        //维系类别
        String parentId = String.valueOf(data.get("parentId"));
        if (!parentId.isEmpty()) {
            sqlBuf.append(" and ct.parent_id = ? ");
            params.add(parentId);
        }
        //持续跟时间--按天查询
        if (!StringUtils.isEmpty(data.get("nextDate"))) {
            sqlBuf.append(" and ct.next_date = ? ");
            params.add(StringUtil.formatNull(data.get("nextDate")));
        }
        //--------------------2017-05-22 增加维系查询 by 王帅---------------------
        // 回访结果 成功或失败
        if (!StringUtils.isEmpty(data.get("visitResult"))) {
            sqlBuf.append(" and exd.visit_result=? ");
            params.add(StringUtil.formatNull(data.get("visitResult")));
        }
        //失败原因
        if (!StringUtils.isEmpty(data.get("failReason"))) {
            sqlBuf.append(" and exd.fail_reason = ? ");
            params.add(StringUtil.formatNull(data.get("fail_reason")));
        }
        //有意进店
        if (!StringUtils.isEmpty(data.get("intentionEnter"))) {
            sqlBuf.append(" and exd.intention_enter= ? ");
            params.add(StringUtil.formatNull(data.get("intentionEnter")));
        }
        //意向等级
        if (!StringUtils.isEmpty(data.get("intentionLevel"))) {
            sqlBuf.append(" and exd.intention_level=? ");
            params.add(StringUtil.formatNull(data.get("intentionLevel")));
        }
        //是否保养
        if (!StringUtils.isEmpty(data.get("ismaintained"))) {
            sqlBuf.append(" and exd.ismaintained=? ");
            params.add(StringUtil.formatNull(data.get("ismaintained")));
        }
        //保养地点
        if (!StringUtils.isEmpty(data.get("maintainAdress"))) {
            sqlBuf.append(" and exd.maintain_adress = ? ");
            params.add(StringUtil.formatNull(data.get("maintainAdress")));
        }
        //流失原因
        if (!StringUtils.isEmpty(data.get("lostReason"))) {
            sqlBuf.append(" and exd.lost_reason = ? ");
            params.add(StringUtil.formatNull(data.get("lostReason")));
        }
        //未保养原因
        if (!StringUtils.isEmpty(data.get("nomaintainReason"))) {
            sqlBuf.append(" and exd.nomaintain_reason ");
            params.add(StringUtil.formatNull(data.get("nomaintainReason")));
        }


        sqlBuf.append(" order by ct.create_time desc ");
    }


    /**
     * 保存任务维系并求助并保存信息
     *
     * @param data
     * @param oid
     * @param uid
     * @throws Exception
     */
    @Override
    public void seekHelpSave(Map data, String oid, String uid) throws Exception {
        this.saveOrUpdate(data, oid, uid);
        TCrmCarTaskHelp help = new TCrmCarTaskHelp();
        help.setCarId(StringUtil.formatEmpty(data.get("carId")));
        help.setPlateNum(StringUtil.formatEmpty(data.get("plateNum")));
        help.setVin(StringUtil.formatEmpty(data.get("vin")));
        help.setSeekHelpTime(DateUtils.getTimeStr24());
        help.setSeekHelpUserId(uid);
        help.setSeekHelpRemark(StringUtil.formatEmpty(data.get("content")));
        help.setSeekHelpUserName(TokenUtil.getUser(uid).get("uName"));
        help.setCreateTime(System.currentTimeMillis());
        help.setCreateUser(uid);
        help.setCompanyId(TokenUtil.getCompanyId(uid));
        help.setStatus(String.valueOf(Constant.SEEK_HELP.init.ordinal()));
        this.crmTaskDao.saveOrUpdate(help);
    }

    /**
     * @param data 实体
     * @return TCrmCarContact
     * @throws Exception
     * @desc 维护记录保存或修改
     * @author 徐钦勇
     */
    @Override
    public void saveOrUpdate(Map data, String oid, String uid) throws Exception {

        /**
         * oid改为部门ID，companyId改为公司ID
         */
        String companyId = oid;

        Map oInfo = TokenUtil.getOrgInfo(uid);
        if(oInfo.size()>0){
           String oidTemp = (String) oInfo.get("deptId");
           if(oidTemp != null){
               oid = oidTemp;
           }
        }


        if (!"".equals(StringUtil.formatEmpty(data.get("expectDate")))) {
            if ("日期选择".equals(StringUtil.formatEmpty(data.get("expectDate")))) {
                data.put("expectDate", "");
            }
        }
        String code = "zdy";
        String pcode = "qt";
        TCrmCarContact contactEntity = new TCrmCarContact();
        TCrmCarMaintain maintainEntity = new TCrmCarMaintain();
        List list = new ArrayList();
        //新增维系记录
        if (data.get("contactName") == null) {
            contactEntity.setContactName(String.valueOf(data.get("linkMan")));
        } else {
            contactEntity.setContactName(String.valueOf(data.get("contactName")));
        }
        if (data.get("contactPhone") == null) {
            contactEntity.setContactPhone(String.valueOf(data.get("linkMobile")));
        } else {
            contactEntity.setContactPhone(String.valueOf(data.get("contactPhone")));
        }
        contactEntity.setContent(String.valueOf(data.get("content") == null ? "" : data.get("content")));
        contactEntity.setRecordInfo(String.valueOf(data.get("recordInfo") == null ? "" : data.get("recordInfo")));
        contactEntity.setRecordLength(String.valueOf(data.get("recordLength") == null ? "" : data.get("recordLength")));
        contactEntity.setRecordPath(String.valueOf(data.get("recordPath") == null ? "" : data.get("recordPath")));
        //有录音文件，证明有通话
        if (contactEntity.getRecordPath().length() > 20) {
            contactEntity.setStatus("1");
        }
        contactEntity.setSaId(uid);
        contactEntity.setCarId(String.valueOf(data.get("carId")));
        contactEntity.setCompanyId(companyId);
        contactEntity.setCreateUser(uid);
        contactEntity.setCreateTime(System.currentTimeMillis());
        contactEntity.setVin(String.valueOf(data.get("vin")));
        contactEntity.setPlateNum(String.valueOf(data.get("plateNum")));
        contactEntity.setParentId(String.valueOf(data.get("parentId")));
        contactEntity.setTitle(String.valueOf(data.get("title")));
        contactEntity.setTypeId(String.valueOf(data.get("typeId")));
        contactEntity.setStoreId(oid);
        contactEntity.setType(String.valueOf(data.get("type")));
        //预约记录默认为0
        contactEntity.setNewRemind("0");

        /*****
         * 根据任务 ID 取跟进的次数，如果有多个任务则取第一个任务
         *
         *
         * *****/
        String taskCount = "";

        //存在任务ID，完成任务
        if (null != data.get("taskId") && data.get("taskId") != "") {
            StringBuffer preTaskId = new StringBuffer();
            String upadetTaskSql = "update " + tableName + " set status='1',update_user=?,update_time=?,sa_id = ? ,deal_date=? where id = ? ";
            //批量处理任务时
            if (data.get("taskId") instanceof ArrayList) {
                List<String> ids = (List) data.get("taskId");
                for (String id : ids) {
                    this.itCrmCarContactDao.executeSQL(upadetTaskSql, uid, System.currentTimeMillis(), uid, DateUtils.getTimeStr("yyyy-MM-dd"), id);
                    Map temp = this.itCrmCarContactDao.info("T_CRM_TASK_REAL", "id", id);
                    // 取第一条任务的持续跟进次数
                    if (taskCount.equals("")) {
                        taskCount = String.valueOf(temp.get("taskCount") == null ? "1" : temp.get(taskCount));
                    }
                    if (temp.get("typePcode").equals("ls") || temp.get("typePcode").equals("by") || temp.get("typePcode").equals("bx")) {
                        code = temp.get("typeCode").toString();
                        pcode = temp.get("typePcode").toString();
                    }

                    preTaskId.append(id).append(",");
                }
                contactEntity.setTaskId(preTaskId.toString());

            }
            //单独处理任务
            else {
                this.itCrmCarContactDao.executeSQL(upadetTaskSql, uid, System.currentTimeMillis(), uid, DateUtils.getTimeStr("yyyy-MM-dd"), String.valueOf(data.get("taskId")));
                Map temp = this.itCrmCarContactDao.info("T_CRM_TASK_REAL", "id", data.get("taskId"));
                if (temp.size() > 0) {
                    if (taskCount.equals("")) {
                        taskCount = String.valueOf(temp.get("taskCount") == null ? "1" : temp.get(taskCount));
                    }
                    if (temp.get("typePcode").equals("ls") || temp.get("typePcode").equals("by") || temp.get("typePcode").equals("bx")) {
                        code = temp.get("typeCode").toString();
                        pcode = temp.get("typePcode").toString();
                    }
                    preTaskId.append(data.get("taskId"));
                    contactEntity.setTaskId(preTaskId.toString());
                }

            }
        }
        /**
         * 增加下次提醒时间
         * 存在下次提醒时间的，对下次提醒内容增加记录
         * **/
        contactEntity.setNextDate(data.get("expectDate") == null ? "" : data.get("expectDate").toString());
        if (!"".equals(contactEntity.getNextDate())) {
            contactEntity.setNewRemind("1");
            String nextContent = data.get("expectContent") == null ? "" : data.get("expectContent").toString();
            if (!nextContent.startsWith("主动延期")) {
                contactEntity.setNextContent("主动延期-" + nextContent);
            }

        }
        //保存维系记录
        this.itCrmCarContactDao.saveOrUpdate(contactEntity);
        //存在提醒时间，生成任务
        this.saveCarContactTask(data, oid, uid, contactEntity, code, pcode, taskCount);
        this.saveCarContactExtend(data, uid, contactEntity);
        this.saveCrmTaskContactInfo(data, contactEntity);
    }

    /**
     * 回写任务维系记录ID
     *
     * @param data
     * @param contactEntity
     * @throws Exception
     */
    private void saveCrmTaskContactInfo(Map data, TCrmCarContact contactEntity) throws Exception {
        StringBuffer sql = new StringBuffer();
        List<String> params = new ArrayList<>();
        sql.append(" update t_crm_task_real set contact_id=? where id in( ");
        params.add(contactEntity.getId());
        if (data.get("taskId") instanceof ArrayList) {
            List<String> ids = (List) data.get("taskId");
            for (int i = 0; i < ids.size(); i++) {
                sql.append(" ? ");
                params.add(ids.get(i));
                if (i < (ids.size() - 1)) {
                    sql.append(" , ");
                }
            }
        } else {
            sql.append(" ? ");
            params.add(StringUtil.formatEmpty(data.get("taskId")));
        }
        sql.append(" ) ");
        this.crmTaskDao.executeSQL(sql.toString(), params.toArray());
    }


    /**
     * 保存维系时，如有提醒内容，生成的任务
     *
     * @param data
     * @param oid
     * @param uid
     * @param contactEntity
     * @throws Exception
     */
    private void saveCarContactTask(Map data, String oid, String uid, TCrmCarContact contactEntity, String code, String pcode, String taskCount) throws Exception {

        if (data.containsKey("expectDate") && data.get("expectDate") != null && data.get("expectDate").toString().length() > 0 && !"日期选择".equals(data.get("expectDate").toString())) {
            String vin = String.valueOf(data.get("vin"));
            String expectDate = String.valueOf(data.get("expectDate"));
            boolean flag = crmTaskDao.checkTaskByVinAndId(oid, vin, StringUtil.formatEmpty(data.get("carId")), code, pcode, expectDate);
            if (!flag) {
                TCrmTaskReal taskEntity = new TCrmTaskReal();
                taskEntity.setCarId(String.valueOf(data.get("carId")));
                String plateNum = "";
                if (data.get("plateNum") == null) {
                    plateNum = vin.substring(vin.length() - 6, vin.length());
                    data.put("plateNum", plateNum);
                }
                taskEntity.setPlateNum(String.valueOf(data.get("plateNum")));
                taskEntity.setContactId(String.valueOf(data.get("contactId") == null ? "" : data.get("contactId")));
                taskEntity.setLinkman(String.valueOf(data.get("linkMan")));
                taskEntity.setLinkphone(String.valueOf(data.get("linkMobile")));
                taskEntity.setStatus("0");
                taskEntity.setIsBack("0");
                taskEntity.setTypeCode(code);
                taskEntity.setTypePCode(pcode);
                taskEntity.setVIN(String.valueOf(data.get("vin")));
                /**
                 * oid改为部门ID，companyId改为公司ID
                 */
                taskEntity.setCompanyId(TokenUtil.getCompanyId(uid));
                taskEntity.setStoreName(getStoreNameByStoreId(oid));
                taskEntity.setExpectDate(String.valueOf(data.get("expectDate")));
                taskEntity.setSaId(uid);
                taskEntity.setCreateUser(uid);
                taskEntity.setCreateTime(System.currentTimeMillis());
                /***临时解决方案，可能出现的问题，标题被修改，默认为一次
                 * 后期改为通过数据库生成
                 * ***/

                String nextContent = String.valueOf(data.get("expectContent"));
                /****
                 * 上一条任务的 taskCount记录数，用于新增记录的标题
                 * ***/
                int count = 0;
                try {
                    count = Integer.valueOf(taskCount) + 1;
                } catch (Exception e) {
                    count = 2;
                }
                taskEntity.setTaskCount(count);
                taskEntity.setContent(nextContent.substring(nextContent.indexOf("次") + 1));
                //增加上次任务ID
                taskEntity.setPreTaskId(contactEntity.getTaskId());
                //有延期任务时设为1
                contactEntity.setNewRemind("1");
                this.itCrmCarContactDao.saveOrUpdate(taskEntity);
            }
        }
    }

    /**
     * 根据门店ID查询门店名称
     * @param StoreId
     * @return
     * @throws Exception
     */
    private String getStoreNameByStoreId(String StoreId) throws Exception{
        String sql  ="SELECT * FROM T_BC_ORGAN WHERE id = ? ";
        Map map = this.crmTaskDao.info(sql,StoreId);
        return (String) map.get("oName");
    }

    /**
     * 保存维系内容
     *
     * @param data
     * @param uid
     * @param contactEntity
     * @throws Exception
     */
    private void saveCarContactExtend(Map data, String uid, TCrmCarContact contactEntity) throws Exception {
        TCrmCarContactResult extendEntity = new TCrmCarContactResult();
        extendEntity.setVin(StringUtil.formatNull(data.get("vin")));
        extendEntity.setcId(contactEntity.getId());
        extendEntity.setResultA(StringUtil.formatNull(data.get("resultA")));
        extendEntity.setResultB(StringUtil.formatNull(data.get("resultB")));
        extendEntity.setResultC(StringUtil.formatNull(data.get("resultC")));
        extendEntity.setResultD(StringUtil.formatNull(data.get("resultD")));
        extendEntity.setTaskPcode(StringUtil.formatNull(data.get("parentId")));
        extendEntity.setTaskCode(StringUtil.formatNull(data.get("typeId")));
        extendEntity.setCompanyId(TokenUtil.getCompanyId(uid));
        extendEntity.setCreateTime(System.currentTimeMillis());
        extendEntity.setCreateUser(uid);
        this.itCrmCarContactDao.saveOrUpdate(extendEntity);
        System.out.print(extendEntity);
    }

    /**
     * @param
     * @return Map
     * @throws Exception
     * @desc 维系明细  + 只查询当前月，当前公司
     * @author 徐钦勇
     */
    @Override
    public List info(String plateNum, String vin, String oid) throws Exception {
        StringBuffer sqlBuf = new StringBuffer();
        List param = new ArrayList();
        String mDate;
        //SimpleDateFormat sf = new SimpleDateFormat("YYYY-MM");
        //mDate = sf.format(new Date());
        sqlBuf.append(" SELECT * from (  ");

        sqlBuf.append("SELECT ");
        sqlBuf.append(" tc.*, ");
        sqlBuf.append("         tu.u_name,tg.company_ip_address,case when length(tg.company_ip_address)>0 and length(tc.record_path)>8  then concat('http://',tg.company_ip_address,'/',SUBSTR(tc.record_path,8)) else tc.record_path end  as record_patha  ");
        sqlBuf.append("  FROM ");
        sqlBuf.append("   t_crm_car_contact tc ");
        sqlBuf.append("  LEFT JOIN t_bc_user tu ON tc.sa_id = tu.id ");
        sqlBuf.append(" left join t_bc_organ tg on tc.store_id=tg.id ");
        sqlBuf.append("   where tc.company_id = ? ");

        param.add(oid);
        // param.add(mDate);
        //由于增加车牌号有些情况下查询不出维系信息 2016-05-25将车牌号查询注释，仅为车驾号 by 王帅
//        if (!plateNum.isEmpty()) {
//            sqlBuf.append("and tc.plate_num = ? ");
//            param.add(plateNum);
//        }
        if (!vin.isEmpty()) {
            sqlBuf.append(" and tc.vin = ? ");
            param.add(vin);
        }
        // 取前20条记录
        sqlBuf.append(" order by create_time desc limit 0,20 ");
        sqlBuf.append(" ) temp ");
        return this.itCrmCarContactDao.list(sqlBuf.toString(), param.toArray());
    }

    /**
     * @param
     * @return Map
     * @throws Exception
     * @desc 关闭任务
     * @author 徐钦勇
     */
    @Override
    public void close(Map data, String oid, String uid) throws Exception {
        //存在任务ID，关闭任务，已关闭2
        if (null != data.get("taskId") && data.get("taskId") != "") {
            String upadetTaskSql = "update " + tableName + " set status='2',update_user=?,update_time=?,sa_id = ? ,deal_date=? ,close_reason = ?,close_time=?,close_user=? where id = ? ";
            //批量处理任务时
            if (data.get("taskId") instanceof ArrayList) {
                List<String> ids = (List) data.get("taskId");
                for (String id : ids) {
                    this.itCrmCarContactDao.executeSQL(upadetTaskSql, uid, System.currentTimeMillis(), uid, DateUtils.getTimeStr("yyyy-MM-dd"), data.get("taskReason"), DateUtils.getTimeStr(), uid, id);
                }
            }
            //单独处理任务
            else {
                this.itCrmCarContactDao.executeSQL(upadetTaskSql, uid, System.currentTimeMillis(), uid, DateUtils.getTimeStr("yyyy-MM-dd"), data.get("taskReason"), DateUtils.getTimeStr(), uid, String.valueOf(data.get("taskId")));
            }
        }
    }

    /**
     * 查询出车辆客户标签
     */
    @Override
    public List UserTag(String vin) throws Exception {
        Map temp = this.itCrmCarContactDao.info("select tags from t_crm_car where vin = ?  limit 1", vin);
        List data = new ArrayList();
        if (temp.get("tags") != null && !temp.get("tags").equals("")) {
            String tempTag = temp.get("tags").toString();
            String[] dataTemp = tempTag.split(",");
            data = Arrays.asList(dataTemp);
            return data;
        } else {
            return data;
        }
    }

    /**
     * 获取选中的标签信息，同时获得选中标签的名称信息
     *
     * @param vin
     * @return
     * @throws Exception
     */
    @Override
    public List UserTagByVin(String uid, String vin) throws Exception {
        List<String> ps = new ArrayList<>();
        ps.add(vin);
        ps.add(TokenUtil.getCompanyId(uid));
        Map temp = this.itCrmCarContactDao.info("select tags from t_crm_car where vin = ? and company_id=? limit 1", ps.toArray());
        List data = new ArrayList();
        if (temp.get("tags") != null && !temp.get("tags").equals("")) {
            String tempTag = temp.get("tags").toString();
            String[] dataTemp = tempTag.split(",");
            StringBuffer sql = new StringBuffer();
            sql.append("select `code` as value,label from T_BC_NEWDICT where p_code in('SHTX','XWTD','YWTX') and `code` in (");
            List<String> params = new ArrayList<>();
            for (int i = 0; i < dataTemp.length; i++) {
                String code = dataTemp[i];
                sql.append("?");
                params.add(code);
                if (i < (dataTemp.length - 1)) {
                    sql.append(",");
                }
            }
            sql.append(" ) ");
            return this.crmTaskDao.list(sql.toString(), params.toArray());
        } else {
            return data;
        }
    }



    String[] excelHeaderRecordout = {"车牌号", "VIN", "车主", "车主电话", "品牌", "车系", "客户经理", "维系内容"};

    @Override
    public XSSFWorkbook exportContact(Map data, String uid) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("维系记录详情");

        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(25);

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        List<TCrmConcatVo> list = concatMapper.export(data, uid);
        TCrmConcatVo vo ;
        for (int i = 0; i < list.size(); i++) {
            vo =  list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(vo.getPlateNum());
            row.createCell(1).setCellValue(vo.getVin());
            row.createCell(2).setCellValue(vo.getContactName());
            row.createCell(3).setCellValue(vo.getContactPhone());
            row.createCell(4).setCellValue(vo.getBrand());
            row.createCell(5).setCellValue(vo.getSeries());
            row.createCell(6).setCellValue(vo.getSaName());
            row.createCell(7).setCellValue(vo.getContent());
        }
        return wb;
    }

    /**
     * 车辆流失分析
     *
     * @param uid
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> carLossMonthStatistics(String uid, Map<String, Object> data) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<Object> params = new ArrayList<>();
        String groupStr = "";
        String labaelName = "";
        String other = "";
        if (!StringUtils.isEmpty(data.get("statisticalCaliber"))) {
            Integer caliber = Integer.valueOf(String.valueOf(StringUtil.formatNull(data.get("statisticalCaliber"))));
            switch (caliber) {
                case 0:
                    labaelName = " exd.task_pcode as name ";
                    groupStr = " group by exd.task_pcode,exd.company_id ";
                    break;
                case 1:
                    labaelName = " exd.maintain_adress as name ";
                    groupStr = " group by exd.maintain_adress,exd.company_id ";
                    other = " and  exd.maintain_adress <>'' ";
                    break;
                case 2:
                    labaelName = " exd.lost_reason as name ";
                    groupStr = " group by exd.lost_reason,exd.company_id ";
                    other = " and  exd.lost_reason <>'' ";
                    break;
                case 3:
                    labaelName = " exd.fail_reason as name ";
                    groupStr = " group by exd.fail_reason,exd.company_id ";
                    other = " and  exd.fail_reason <>'' ";
                    break;
            }
        }
        String companyId = StringUtil.formatEmpty(data.get("companyId"));
        String area = StringUtil.formatEmpty(data.get("area"));
        String brand = StringUtil.formatEmpty(data.get("brand"));

        sqlStr.append(" select count(exd.id) value, ").append(labaelName);
//        sqlStr.append(" from t_crm_car_contact_extend exd,t_crm_car car ");
        sqlStr.append(" from t_crm_car_contact_extend exd ");
        sqlStr.append(",");
        sqlStr.append(" (select * from t_bc_organ  where 1=1  ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlStr, params, "");
        sqlStr.append(" ) bc ");
//        sqlStr.append(" where exd.vin = car.vin and exd.company_id=car.company_id and exd.company_id=? ");
        sqlStr.append(" where bc.id=exd.company_id ");
//        params.add(TokenUtil.getCompanyId(uid));
        //开始时间
        if (!StringUtils.isEmpty(data.get("startDate"))) {
            String startDate = StringUtil.formatNull(data.get("startDate"));
            sqlStr.append(" and exd.create_time>=? ");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Long startTimeLong = sdf.parse(startDate).getTime();
            params.add(startTimeLong);
        }
        //结束时间
        if (!StringUtils.isEmpty(data.get("stopDate"))) {
            String endDate = StringUtil.formatNull(data.get("stopDate"));
            endDate = endDate.substring(0, 10) + " 23:59:59";
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Long stopTimeLong = sdf.parse(endDate).getTime();
            sqlStr.append(" and exd.create_time<=? ");
            params.add(stopTimeLong);
        }
        //carCtype车辆小类
        if (!StringUtils.isEmpty(data.get("carCtype"))) {
            String carCtype = StringUtil.formatNull(data.get("carCtype"));
            sqlStr.append(" and exd.task_code=? ");
            params.add(carCtype);
        }
        //carPtype车辆大类
        if (!StringUtils.isEmpty(data.get("carPtype"))) {
            String carPtype = StringUtil.formatNull(data.get("carPtype"));
            sqlStr.append(" and exd.task_pcode=? ");
            params.add(carPtype);
        }
        //lostDirection流失方向
        if (!StringUtils.isEmpty(data.get("lostDirection"))) {
            String lostDirection = StringUtil.formatNull(data.get("lostDirection"));
            sqlStr.append(" and exd.maintain_adress=? ");
            params.add(lostDirection);
        }
        //lostReason流失原因
        if (!StringUtils.isEmpty(data.get("lostReason"))) {
            String lostReason = StringUtil.formatNull(data.get("lostReason"));
            sqlStr.append(" and exd.lost_reason=? ");
            params.add(lostReason);
        }
        //lost_reason回访失败
        if (!StringUtils.isEmpty(data.get("failReason"))) {
            String failReason = StringUtil.formatNull(data.get("failReason"));
            sqlStr.append(" and exd.fail_reason=? ");
            params.add(failReason);
        }
        sqlStr.append(other);
        sqlStr.append(groupStr);
        List<Map<String, Object>> list = this.itCrmCarContactDao.list(sqlStr.toString(), params.toArray());
        this.analyList(list);
        return list;
    }

    /**
     * 车辆流失明细查询
     *
     * @param uid
     * @param data
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map<String, Object> carLossMonthStatisticsDetail(String uid, Map<String, Object> data, String start, String limit) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<Object> params = new ArrayList<>();
        this.createTosql(uid, data, sqlStr, params);
        return this.itCrmCarContactDao.page(sqlStr.toString(), params.toArray(), start, limit);
    }

    /**
     * 车辆流失明细查询sql
     *
     * @param uid
     * @param data
     * @param sqlStr
     * @param params
     */
    private void createTosql(String uid, Map<String, Object> data, StringBuffer sqlStr, List<Object> params) throws Exception {
        String companyId = StringUtil.formatEmpty(data.get("companyId"));
        String area = StringUtil.formatEmpty(data.get("area"));
        String brand = StringUtil.formatEmpty(data.get("brand"));
        sqlStr.append(" SELECT ");
        sqlStr.append(" CASE " +
                "WHEN exd.visit_result ='success' THEN '成功' " +
                "WHEN exd.visit_result ='fail' THEN '失败' end as visit_result, ");
        sqlStr.append(
                " CASE " +
                        "WHEN exd.task_pcode = 'zh' THEN " +
                        " '综合' " +
                        "WHEN exd.task_pcode = 'by' THEN " +
                        " '保养' " +
                        "WHEN exd.task_pcode = 'bx' THEN " +
                        " '保险' " +
                        "WHEN exd.task_pcode = 'ls' THEN " +
                        " '流失' " +
                        "WHEN exd.task_pcode = 'ga' THEN " +
                        " '关爱' " +
                        "END AS task_pcode, " +
                        " CASE " +
                        "WHEN exd.task_code = 'sc' THEN " +
                        " '手册' " +
                        "WHEN exd.task_code = 'xbx' THEN " +
                        " '新保续' " +
                        "WHEN exd.task_code = 'xubx' THEN " +
                        " '续保续' " +
                        "WHEN exd.task_code = 'hy' THEN " +
                        " '活跃' " +
                        "WHEN exd.task_code = 'fhy' THEN " +
                        " '非活跃' " +
                        "WHEN exd.task_code = 'sr' THEN " +
                        " '生日' " +
                        "WHEN exd.task_code = 'ns' THEN " +
                        " '年审' " +
                        "WHEN exd.task_code = 'rc' THEN " +
                        " '日常' " +
                        "WHEN exd.task_code = 'yb' THEN " +
                        " '延保' " +
                        "WHEN exd.task_code = 'hd' THEN " +
                        " '活动' " +
                        "WHEN exd.task_code = 'srhf' THEN " +
                        " '三日回访' " +
                        "WHEN exd.task_code = 'zls' THEN " +
                        " '准流失' " +
                        "WHEN exd.task_code = 'yls' THEN " +
                        " '流失' " +
                        "WHEN exd.task_code = 'sb' THEN " +
                        " '首保' " +
                        "WHEN exd.task_code = 'sbm' THEN " +
                        " '目标月首保' " +
                        "WHEN exd.task_code = 'eb' THEN " +
                        " '二保' " +
                        "WHEN exd.task_code = 'ebm' THEN " +
                        " '目标月二保' " +
                        "WHEN exd.task_code = 'db' THEN " +
                        " '定保' " +
                        "END AS task_code, " +
                        " exd.maintain_adress, " +
                        " CASE " +
                        "WHEN exd.lost_reason = 'clzr' THEN " +
                        " '车辆转让或报废' " +
                        "WHEN exd.lost_reason = 'rzwd' THEN " +
                        " '人在外地没时间保养' " +
                        "WHEN exd.lost_reason = 'glc' THEN " +
                        " '过路车' " +
                        "WHEN exd.lost_reason = 'chsy' THEN " +
                        " '车很少用' " +
                        "WHEN exd.lost_reason = 'fbr' THEN " +
                        " '非车主本人接听电话' " +
                        "WHEN exd.lost_reason = 'qdc' THEN " +
                        " '外地车' " +
                        "WHEN exd.lost_reason = 'wxzl' THEN " +
                        " '维修质量' " +
                        "WHEN exd.lost_reason = 'wxjg' THEN " +
                        " '维修价格' " +
                        "WHEN exd.lost_reason = 'wxxl' THEN " +
                        " '维修效率' " +
                        "WHEN exd.lost_reason = 'fwtd' THEN " +
                        " '服务态度' " +
                        "WHEN exd.lost_reason = 'fwhj' THEN " +
                        " '服务环境' " +
                        "WHEN exd.lost_reason = 'lcy' THEN " +
                        " '路程远' " +
                        "WHEN exd.lost_reason = 'qt' THEN " +
                        " '其他' " +
                        "END AS lost_reason, " +
                        " CASE " +
                        "WHEN exd.fail_reason = 'dhtj' THEN " +
                        " '电话错误停机或空号' " +
                        "WHEN exd.fail_reason = 'rybf' THEN " +
                        " '人员不符' " +
                        "WHEN exd.fail_reason = 'wjt' THEN " +
                        " '未接通' " +
                        "WHEN exd.fail_reason = 'khjf' THEN " +
                        " '客户拒访' " +
                        "END AS fail_reason, " +
                        " DATE_FORMAT( " +
                        " FROM_UNIXTIME(exd.create_time / 1000), " +
                        " '%Y-%m-%d' " +
                        ") lsDate, " +
                        " case WHEN exd.intention_level ='lcwd'  THEN '里程未到'            " +
                        "WHEN exd.intention_level ='jqdd'  THEN '近期到店'            " +
                        "WHEN exd.intention_level ='zjtm'  THEN '最近太忙没时间进店'          " +
                        "WHEN exd.intention_level ='yyyh'  THEN '已预约好近期到店' end as intention_Level,  " +
                        "case WHEN exd.intention_enter='yes' then '是'  " +
                        "  WHEN exd.intention_enter='no' then '否' end as intention_enter  " +
                        ",case WHEN exd.ismaintained='yes' then '是'  " +
                        "  WHEN exd.ismaintained='no' then '否' end as ismaintained," +
                        " car.plate_num, " +
                        " car.vin, " +
                        " car.owner_name,exd.company_id,exd.o_name " +
                        "FROM " +
                        " (select exd.*,bc.o_name from t_crm_car_contact_extend exd, ");
        sqlStr.append(" (select * from t_bc_organ  where 1=1  ");
        Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sqlStr, params, "");
        sqlStr.append(" ) bc where exd.company_id=bc.id");
        sqlStr.append(" )exd, ");
        sqlStr.append(" t_crm_car car " +
                "WHERE " +
                " exd.vin = car.vin AND exd.car_id=car.id and exd.company_id=car.company_id ");
        //开始时间
        if (!StringUtils.isEmpty(data.get("startDate"))) {
            String startDate = StringUtil.formatNull(data.get("startDate"));
            sqlStr.append(" and exd.create_time>=? ");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Long startTimeLong = sdf.parse(startDate).getTime();
            params.add(startTimeLong);
        }
        //结束时间
        if (!StringUtils.isEmpty(data.get("stopDate"))) {
            String endDate = StringUtil.formatNull(data.get("stopDate"));
            endDate = endDate.substring(0, 10) + " 23:59:59";
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Long stopTimeLong = sdf.parse(endDate).getTime();
            sqlStr.append(" and exd.create_time<=? ");
            params.add(stopTimeLong);
        }
        //carCtype车辆小类
        if (!StringUtils.isEmpty(data.get("carCtype"))) {
            String carCtype = StringUtil.formatNull(data.get("carCtype"));
            sqlStr.append(" and exd.task_code=? ");
            params.add(carCtype);
        }
        //carPtype车辆大类
        if (!StringUtils.isEmpty(data.get("carPtype"))) {
            String carPtype = StringUtil.formatNull(data.get("carPtype"));
            sqlStr.append(" and exd.task_pcode=? ");
            params.add(carPtype);
        }
        //lostDirection流失方向
        if (!StringUtils.isEmpty(data.get("lostDirection"))) {
            String lostDirection = StringUtil.formatNull(data.get("lostDirection"));
            sqlStr.append(" and exd.maintain_adress=? and exd.maintain_adress<>''");
            params.add(lostDirection);
        }
        //lostReason流失原因
        if (!StringUtils.isEmpty(data.get("lostReason"))) {
            String lostReason = StringUtil.formatNull(data.get("lostReason"));
            sqlStr.append(" and exd.lost_reason=? and exd.lost_reason<>'' ");
            params.add(lostReason);
        }
        //lost_reason回访失败
        if (!StringUtils.isEmpty(data.get("failReason"))) {
            String failReason = StringUtil.formatNull(data.get("failReason"));
            sqlStr.append(" and exd.fail_reason=? and exd.fail_reason<>'' ");
            params.add(failReason);
        }
    }


    private void analyList(List<Map<String, Object>> list) {
        Map<String, String> nameMap = this.concatNameMap();
        for (Map<String, Object> map : list) {
            String name = String.valueOf(map.get("name"));
            if ("".equals(name)) {
                name = "其它";
            } else {
                if (nameMap.get(name) != null) {
                    name = nameMap.get(name);
                }
            }
            map.put("name", name);
        }
    }

    private Map concatNameMap() {
        Map<String, String> map = new HashMap<>();
        map.put("by", "保养");
        map.put("ga", "关爱");
        map.put("ls", "流失");
        map.put("srhf", "三日回访");
        map.put("bx", "保险");
        map.put("zh", "综合");

        map.put("clzr", "车辆转让或报废");
        map.put("rzwd", "人在外地没时间保养");
        map.put("glc", "过路车");
        map.put("chsy", "车很少用");
        map.put("fbr", "非车主本人接听电话");
        map.put("qdc", "外地车");
        map.put("wxzl", "维修质量");
        map.put("wxjg", "维修价格");
        map.put("wxxl", "维修效率");
        map.put("fwtd", "服务态度");
        map.put("fwhj", "服务环境");
        map.put("lcy", "路程远");
        map.put("qt", "其他");

        map.put("dhtj", "电话错误停机或空号");
        map.put("rybf", "人员不符");
        map.put("wjt", "未接通");
        map.put("khjf", "客户拒访");
        map.put("sc", "手册");
        map.put("sr", "生日");
        map.put("sb", "首保");
        return map;
    }

    /**
     * 查询车辆流失明细导出Excel
     *
     * @param uid
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public XSSFWorkbook exportExcelBycarLossMonthStatistics(String uid, Map<String, Object> data) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        List<Object> params = new ArrayList<>();
        this.createTosql(uid, data, sqlStr, params);
        List<Map<String, Object>> list = this.itCrmCarContactDao.list(sqlStr.toString(), params.toArray());
        return createExcel(list, uid);
    }


    public XSSFWorkbook createExcel(List<Map<String, Object>> lists, String uid) {
        String[] excelHeaderRecordout = {"车主", "车牌号", "车驾号", "车辆大类", "车辆细类", "流失方向", "流失原因", "回访失败", "流失日期"};

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFSheet sheet = wb.createSheet("车辆流失详情");


        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < lists.size(); i++) {
            Map map = (Map) lists.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(String.valueOf(map.get("ownerName")));
            row.createCell(1).setCellValue(String.valueOf(map.get("plateNum")));
            row.createCell(2).setCellValue(String.valueOf(map.get("vin")));
            row.createCell(3).setCellValue(String.valueOf(map.get("taskPcode")));
            row.createCell(4).setCellValue(String.valueOf(map.get("taskCode")));
            row.createCell(5).setCellValue(StringUtil.formatNull(map.get("maintainAdress")));
            row.createCell(6).setCellValue(StringUtil.formatNull(map.get("lostReason")));
            row.createCell(7).setCellValue(StringUtil.formatNull(map.get("failReason")));
            row.createCell(8).setCellValue(StringUtil.formatNull(map.get("lsDate")));
        }
        return wb;
    }

    /**
     * 获取录音文件路径
     *
     * @param uid
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public Map getRecordAddress(String uid, String id) throws Exception {
        Map data = new HashMap();
        String url = "";
        //根据用户获取公司录音IP地址
        StringBuffer getRecordIpAddressSql = new StringBuffer();
        getRecordIpAddressSql.append("select company_ip_address from t_bc_organ where id = ? ");
        String oid = TokenUtil.getCompanyId(uid);
        Map getRecordIpAddress = this.itCrmCarContactDao.info(getRecordIpAddressSql.toString(), oid);
        //根据维系id获取录音文件名
        StringBuffer getRecordFileNameByIdSql = new StringBuffer();
        getRecordFileNameByIdSql.append("select record_path from t_crm_car_contact where id = ? ");
        Map getRecordFileNameById = this.itCrmCarContactDao.info(getRecordFileNameByIdSql.toString(), id);
        if (getRecordIpAddress.size() > 0 && getRecordFileNameById.size() > 0) {
            if (getRecordIpAddress.get("companyIpAddress") != "" && !StringUtils.isEmpty(getRecordIpAddress.get("companyIpAddress"))) {
                if (getRecordFileNameById.get("recordPath") != "" && !StringUtils.isEmpty(getRecordFileNameById.get("recordPath"))) {
                    url = getRecordIpAddress.get("companyIpAddress") + port + String.valueOf(getRecordFileNameById.get("recordPath")).substring(7);
                } else {
                    throw new Exception("未找到录音文件！");
                }
            } else {
                throw new Exception("未给公司配录音地址！");
            }
        }
        data.put("url", url);
        return data;
    }
}
